<?php

namespace App\Repository\Impl;

if (!defined('IN_PX'))
    exit;

use App\Repository\Archives;
use PDO;

class ArchivesImpl implements Archives {

    //仓储层组件
    private function __Repository($value = 'archives') {}

    private function __Inject($db) {}


    public function aliasId($language, $href) {
        if ($language == 1) {
            $href = 'en/' . $href;
        }
        return $href;
    }
    
//    public function count($categoryId, $__Inject = array('$db')) {
    public function count($categoryId) {
        return intval($this->db->table('`#@__@archives`')
            ->where('`category_id` = ? AND `is_display` = 1 AND `is_type` = 0')
            ->bind(array($categoryId))
            ->count());
    }

    public function find($id) {
        $_field = is_numeric($id) ? 'archives_id' : 'seo_url';
        return $this->db->select('a.*, ars.*, c.`level`, c.`id_tree`, c.`parent_id`,'
            . 'c.`root_id`, c.`is_part`, c.`channel_type`, c.`nav_type`')
            ->table('`#@__@archives` a, `#@__@archives_substance` ars, `#@__@category` c')
            ->where("a.`{$_field}` = ? AND a.`is_display` = 1 AND a.`is_type` = 0 AND a.`archives_id` = ars.`archives_id`"
                . ' AND a.`category_id` = c.`category_id` AND c.`is_display` = 1')
            ->bind(array($id))
            ->find();
    }

    public function certificate($category, $start = 0, $end = 9) {
        return $this->db->cacheable()->select()
            ->table('`#@__@certificate`')
            ->where('is_status = 1 AND `category_id` = ?')
            ->bind(array($category))
            ->limit($start, $end)
            ->order('sort', 'ASC')
            ->findAll();
    }

    public function certificateCount($category) {
        return $this->db->table('`#@__@certificate`')
            ->where('is_status = 1 AND `category_id` = ?')
            ->bind(array($category))
            ->count();
    }

    //点击次数加1
    public function viewCount($id) {
        $this->db->nonCacheable()->table('`#@__@archives`')->row(array(
            'view_count' => 'view_count + 1'
        ))->where('archives_id = ?')->bind(array(
            $id
        ))->update();
    }

    public function findAll($categoryId, $start = null, $end = null) {
        $this->db->debug();
        return $this->db->select('a.`archives_id`, a.`title`, a.`cover`, a.`title_english`, a.`seo_url`, a.`synopsis`'
            . ', a.`cover`, a.`is_status`, a.`release_date`, a.`add_date`, a.`language`, a.`attachment`, a.`chain`, asb.`substance`, 
            c.`channel_type`, c2.`list_dir`, a.`video_url`, a.`recruits_number`, a.`recruits_description`, a.`requirements`, a.`salary`, a.`mailbox`, a.`is_login`')
            ->table('`#@__@archives` a, `#@__@archives_substance` asb, `#@__@category` c, `#@__@category` c2')
            ->where('a.`category_id` = ? AND a.`is_display` = 1 AND a.`is_type` = 0 AND a.`category_id` = c.`category_id`'
                . ' AND c.`root_id` = c2.`category_id` AND asb.`archives_id` = a.`archives_id`')
            ->order('a.`release_date`')
            ->limit($start, $end)
            ->bind(array($categoryId))
            ->findAll();
    }

    public function findAllCount($categoryId) {
        $this->db->debug();
        return $this->db->table('`#@__@archives` a, `#@__@archives_substance` asb, `#@__@category` c, `#@__@category` c2')
            ->where('a.`category_id` = ? AND a.`is_display` = 1 AND a.`is_type` = 0 AND a.`category_id` = c.`category_id`'
                . ' AND c.`root_id` = c2.`category_id` AND asb.`archives_id` = a.`archives_id`')
            ->bind(array($categoryId))
            ->count();
    }

    private function _prevNext($date, $categoryId, $prevOrNext) {
        if ($prevOrNext == '>') {
            $release_date = 'a.`release_date`';
            $sort = 'ASC';
        } else {
            $release_date = 'a.`release_date`';
            $sort = 'DESC';
        }
        return $this->db->select('a.`archives_id`, a.`title`, a.`seo_url`, a.`language`, c.`channel_type`, c2.`list_dir`')
            ->table('`#@__@archives` a, `#@__@category` c, `#@__@category` c2')
            ->where("a.`release_date` {$prevOrNext} ? AND a.`category_id` = ? AND a.`is_display` = 1 AND a.`is_type` = 0"
                . ' AND a.`category_id` = c.`category_id` AND c.`root_id` = c2.`category_id`')
            ->bind(array($date, $categoryId))
            ->order($release_date, $sort)
            ->find();
    }

    public function prev($date, $categoryId) {
        return $this->_prevNext($date, $categoryId, '<');
    }

    public function next($date, $categoryId) {
        return $this->_prevNext($date, $categoryId, '>');
    }

    /**
     * 可用于循环中[chains()]
     * @return mixed
     */
    public function getAnchorText() {
        return $this->db->chains()->select('`text`, `link_url`')
            ->table('`#@__@anchor_text`')
            ->where('`is_status` = 1')
            ->order('LENGTH(`text`) ASC, `anchor_text_sort`', 'ASC')
            ->findAll();
    }

    public function getFooterLink($categoryId = 0, $isHome = false) {
        if ($isHome) { //首页显示
            $_where = '(`category_id` = ? OR `link_type` = 1) AND `is_status` = 1';
        } else {
            $_where = '`language` = ? AND `is_status` = 1';
        }
//        $this->db->debug();
        return $this->db->select()
            ->table('`#@__@footer_link`')
            ->where($_where)
            ->bind(array($categoryId))
            ->order('footer_link_id', 'ASC')
            ->findAll();
    }

    public function getTagsList($id) {
        return $this->db->mode(PDO::FETCH_COLUMN)->select('t.`tags_text`')
            ->table('`#@__@tags_list` tl, `#@__@tags` t')
            ->where('tl.`archives_id` = ? AND tl.`tags_id` = t.`tags_id`')
            ->bind(array($id))
            ->order('t.tags_id', 'ASC')
            ->findAll();
    }

    public function getTags() {
        return $this->db->select()
            ->table('`#@__@tags`')
            ->order('tags_id', 'ASC')
            ->findAll();
    }

    //首页banner图显示
    public function getAd($language, $typeId = 0) {
        return $this->db->select('`ad_title`, `ad_img`, `ad_url`')
            ->table('`#@__@ad`')
            ->where('`language` = ? AND `type_id` = ? AND `is_display` = 1')
            ->bind(array($language, $typeId))
            ->order('`ad_sort`', 'ASC')
            ->findAll();
    }

    public function findLogo(){
        return $this->db->select('`synopsis`')
            ->table('`#@__@sys_setting`')
            ->where('`setting_id` = 12')
            ->find();
    }

    public function findHome($channelType, $language = 0, $start = 0, $end = 1000) {
        $_where = ' a.`category_id` = c.`category_id` AND a.`is_display` = 1 AND a.`is_type` = 0 AND a.`is_home_display` = 1 AND c.`channel_type` = ? AND c.`language` = ?';
        $_order = ' a.`sort` DESC, a.`is_status` DESC, a.`release_date` ';


        return $this->db->cacheable()->select('a.`archives_id`, a.`title_english`, a.`title`,a.`synopsis`'
            . ', a.`cover`, a.`attachment`, a.`is_status`, a.`release_date` , a.`add_date`, a.`language`, c.`channel_type`, c.`list_dir`')
            ->table('`#@__@archives` a, `#@__@category` c')
            ->where($_where)
            ->order($_order)
            ->limit($start, $end)
            ->bind(array($channelType, $language))
            ->findAll();
    }

    public function findHomeNews($channelType, $language = 0, $start = null, $end = null) {
        $_where = ' a.`category_id` = c.`category_id` AND a.`is_display` = 1 AND a.`is_home_display` = 1 AND c.`category_id` = ? AND c.`language` = ?';
        $this->db->debug();
        return $this->db->cacheable()->select('a.`archives_id`, a.`title_english`, a.`title`,a.`synopsis`'
            . ', a.`cover`, a.`attachment`, a.`is_status`, a.`release_date` , a.`add_date`, a.`language`, c.`channel_type`, c.`list_dir`')
            ->table('`#@__@archives` a, `#@__@category` c')
            ->where($_where)
            ->bind(array($channelType, $language))
            ->order('a.`release_date`', 'DESC')
            ->limit($start, $end)
            ->findAll();
    }

    public function findHomeProduct($categoryId, $memberId, $start = null, $end = null, $isHome = false) {
        $_where = ' a.`is_display` = 1 AND a.`is_type` = 0 AND c.`root_id` = ?';
        $_order = ' a.`sort` DESC, a.`is_status` DESC, a.`release_date` ';
        if ($isHome) {
            $_where =  ' a.`is_display` = 1 AND a.`is_type` = 0 AND c.`category_id` = ? AND a.`is_status` = 3';
//            $_order = 'a.`sort` DESC, a.`is_status`, a.`release_date`' ;
        }
        $this->db->debug();
        return $this->db->select('a.`archives_id`, a.`title_english`, a.`title`, a.`title`, a.`seo_url`, a.`synopsis`'
            . ', a.`cover`, a.`is_status`, a.`release_date`, a.`add_date`, a.`video_url`, a.`language`, a.`attachment`, a.`chain`, a.`is_login`, c.`channel_type`, co.`collection_id`')
            ->table('`#@__@archives` a LEFT JOIN `#@__@collection` co ON a.archives_id = co.archives_id AND co.`member_id` = ? AND co.`is_status` = 1 AND co.`is_type` = 0 LEFT JOIN `#@__@category` c ON a.`category_id` = c.`category_id`')
            ->where($_where)
            ->order($_order)
            ->limit($start, $end)
            ->bind(array($memberId, $categoryId))
            ->findAll();
    }

    public function findProduct($categoryId, $memberId, $start = null, $end = null) {
        $_where = ' a.`is_display` = 1 AND a.`is_type` = 0 AND c.`category_id` = ?';
        $_order = ' a.`sort` DESC, a.`is_status` DESC, a.`release_date` ';
        $this->db->debug();
        return $this->db->select('a.`archives_id`, a.`title_english`, a.`title`, a.`title`, a.`seo_url`, a.`synopsis`'
            . ', a.`cover`, a.`is_status`, a.`release_date` , a.`add_date`, a.`video_url`, a.`language`, a.`attachment`, a.`chain`, a.`is_login`, c.`channel_type`, co.`collection_id`')
            ->table('`#@__@archives` a LEFT JOIN `#@__@category` c ON a.`category_id` = c.`category_id` LEFT JOIN `#@__@collection` co ON a.archives_id = co.archives_id AND co.`member_id` = ? AND co.`is_status` = 1 AND co.`is_type` = 0')
            ->where($_where)
            ->order($_order)
            ->limit($start, $end)
            ->bind(array($memberId, $categoryId))
            ->findAll();
    }

    public function HomeProductList($categoryId) {
        $_where = ' a.`category_id` = ? AND a.`is_display` = 1 AND a.`is_type` = 0 AND a.`is_home_display` = 1';
        $_order = ' a.`sort`';

//        $this->db->debug();
        return $this->db->select('a.`archives_id`, a.`title`, a.`cover`, a.`archives_id`')
            ->table('`#@__@archives` a')
            ->where($_where)
            ->order($_order)
            ->bind(array($categoryId))
            ->findAll();
    }

    public function findHomeArchives($categoryId, $start = null, $end = null, $isHome = false) {
        $_where = ' a.`category_id` = c.`category_id` AND a.`is_display` = 1 AND a.`is_type` = 0 AND c.`root_id` = ?';
        $_order = ' a.`sort` DESC, a.`is_status` DESC, a.`release_date` ';
        if ($isHome) {
            $_where =  ' a.`category_id` = c.`category_id` AND a.`is_display` = 1 AND a.`is_type` = 0 AND c.`category_id` = ? AND a.`is_status` = 3';
//            $_order = 'a.`sort` DESC, a.`is_status`, a.`release_date`' ;
        }
        return $this->db->select('a.`archives_id`, a.`title_english`, a.`title`, a.`title`, a.`seo_url`, a.`synopsis`'
            . ', a.`cover`, a.`is_status`, a.`release_date` , a.`add_date`, a.`video_url`, a.`language`, a.`attachment`, a.`chain`, a.`is_login`, c.`channel_type`')
            ->table('`#@__@archives` a, `#@__@category` c')
            ->where($_where)
            ->order($_order)
            ->limit($start, $end)
            ->bind(array($categoryId))
            ->findAll();
    }

    public function findHomeArchivesCount($categoryId) {
        return $this->db->select('a.`archives_id`,a.`title_english`, a.`title`, a.`seo_url`, a.`synopsis`'
            . ', a.`cover`, a.`is_status`, a.`release_date`, a.`language`, c.`channel_type`')
            ->table('`#@__@archives` a, `#@__@category` c')
            ->where(' a.`category_id` = c.`category_id` AND a.`is_display` = 1 AND a.`is_type` = 0 AND c.root_id = ?')
            ->order('a.`is_status` DESC, a.`release_date`')
            ->bind(array($categoryId))
            ->count();
    }

    //详情页的多图显示
    public function findArchivesSrc($id) {
        return $this->db->select('*')
            ->table('`#@__@archives_attach`')
            ->where('`archives_id` = ?')
            ->bind(array($id))
            ->findAll();
    }

    //首页推荐(一级导航的全部推荐)
    public function getRecommendation($categoryId)
    {
        $this->db->debug();
        return $this->db->select('a.`archives_id`, a.`title`, a.`cover`, a.`title_english`, a.`seo_url`, a.`synopsis`'
            . ', a.`cover`, a.`is_status`, a.`release_date`, a.`add_date`, a.`language`, a.`attachment`, asb.`substance`, 
            c.`channel_type`, c2.`list_dir`, a.`location`, a.`experience`')
            ->table('`#@__@archives` a, `#@__@archives_substance` asb, `#@__@category` c, `#@__@category` c2')
            ->where('c.`parent_id` = ? AND a.`is_display` = 1 AND a.`is_type` = 0 AND a.`is_home_display` = 1 AND a.`category_id` = c.`category_id`'
                . ' AND c.`root_id` = c2.`category_id` AND asb.`archives_id` = a.`archives_id`')
            ->order('a.`is_status` DESC, a.`sort`')
            ->bind(array($categoryId))
            ->findAll();
    }

    public function productPics($id){
        return $this->db->select('`src`')
            ->table('`#@__@archives_attach`')
            ->where('`archives_id` = ? ')
            ->bind(array($id))
            ->findAll();
    }

    public function productLink($id){
        return $this->db->select('a.`archives_id`, a.`title`')
            ->table('`#@__@archives_goods` ag, `#@__@archives` a')
            ->where('ag.`archives_id` = ? AND ag.`like_archives_id` = a.`archives_id`')
            ->bind(array($id))
            ->findAll();
    }

    public function findInvite($categoryId, $start, $end) {
        return $this->db->select('*')
            ->table('`#@__@recruit_type`')
            ->where('`category_id` = ? ')
            ->bind(array($categoryId))
            ->limit($start, $end)
            ->findAll();
    }

    public function findInviteTotal($categoryId) {
        return $this->db->table('`#@__@recruit_type`')
            ->where('`category_id` = ? ')
            ->bind(array($categoryId))
            ->count();
    }

    public function findCurrent($categoryId, $start, $end) {
        $this->db->debug();
        return $this->db->select('a.*')
            ->table('`#@__@recruit_type` t, `#@__@archives` a')
            ->where('t.`type_id` = ? AND t.`type_id` = a.`category_id` AND a.`is_display` = 1 AND a.`is_type` = 1')
            ->bind(array($categoryId))
            ->limit($start, $end)
            ->findAll();
    }

    public function findCurrentTotal($categoryId) {
        return $this->db->table('`#@__@recruit_type` t, `#@__@archives` a')
            ->where('t.`type_id` = ? AND t.`type_id` = a.`category_id` AND a.`is_display` = 1 AND a.`is_type` = 1')
            ->bind(array($categoryId))
            ->count();
    }

    public function findProductAll($channelType, $title, $language = 0)
    {
        $_where = 'c.`channel_type` = ' . $channelType . ' AND c.`language` = ' . $language . ' AND a.`is_display` = 1 AND a.`is_type` = 0 AND a.`category_id` = c.`category_id`';
        $_bind = array();

        if (isset($title) && $title != '') {
            $_where .= ' AND a.title LIKE :title';
            $_bind[':title'] = '%' . trim($title) . '%';
        }
        return $this->db->select('a.*')
            ->table('`#@__@archives` a, `#@__@category` c')
            ->where($_where)
            ->order('a.`is_status` DESC, a.`sort`')
            ->bind($_bind)
            ->findAll();
    }

    public function findCollegeAll($channelType, $get, $language = 0)
    {
        $_where = 'c.`channel_type` = ' . $channelType . ' AND c.`language` = ' . $language . ' AND a.`is_display` = 1 AND a.`is_type` = 0 AND a.`category_id` = c.`category_id`';
        $_bind = array();

        if (isset($get) && $get['title'] != '') {
            $_where .= ' AND a.title LIKE :title';
            $_bind[':title'] = '%' . trim($get['title']) . '%';
        }

        if (isset($get) && $get['collegeTime'] != 0) {
            $_where .= ' AND a.add_date >= :collegeTime';
            $_bind[':collegeTime'] = (time() - $get['collegeTime'] * 86400);
        }

        if (isset($get) && $get['type'] != 0) {
            $_where .= ' AND a.category_id = :category_id';
            $_bind[':category_id'] = $get['type'];
        }

        return $this->db->select('a.*')
            ->table('`#@__@archives` a, `#@__@category` c')
            ->where($_where)
            ->order('a.`is_status` DESC, a.`sort`')
            ->bind($_bind)
            ->findAll();
    }

    public function findRecruitAll($table) {
        return $this->db->select('*')
            ->table($table)
            ->where('`is_display` = 1')
            ->order('`sort`', 'DESC')
            ->findAll();
    }

    public function findCertificate($type, $status = 0, $title = '') {
        $_where = '`type_id` = :type_id AND `is_status` = :is_status';
        $_bind = array();
        $_bind[':type_id'] = $type;
        $_bind[':is_status'] = $status;

        if ($title != '') {
            $_where .= ' AND (`title` LIKE :strSearchKeyword OR `synopsis` LIKE :strSearchKeyword)';
            $_bind[':strSearchKeyword'] = '%' . trim($title) . '%';
        }
        $this->db->debug();
        return $this->db->select('*')
            ->table('`#@__@certificate`')
            ->where($_where)
            ->bind($_bind)
            ->order('`sort`', 'DESC')
            ->findAll();
    }

    public function findMap() {
        return $this->db->select('*')
            ->table('`#@__@map`')
            ->findAll();
    }

    //首页banner图显示
    public function getSynopsis($language, $typeId = 0) {
        return $this->db->select('*')
            ->table('`#@__@synopsis`')
            ->where('`language` = ? AND `type_id` = ? AND `is_display` = 1')
            ->bind(array($language, $typeId))
            ->order('`ad_sort`', 'ASC')
            ->findAll();
    }

}
